"""
@author：yiwang
@file_name: __init__.py
@create date: 2023-09-01
@file_description：
"""
from apps.NGS_API.common.common_time import *
from flask_restful import reqparse

from apps.NGS_API.connector.db_common import query_info

class create_msyql_sql(object):
    def __init__(self,data_table):
        self.data_table = data_table
        
    def create_query_infos(self):
        table_cols = ""
        for _,values in self.data_table["match"].items():
            values = values.split("|")[0]
            table_cols = table_cols + values + ","
        table_cols = table_cols.rstrip(",")
        query_sql = '''
            SELECT
                {table_cols}
            FROM 
                {table_name}
        '''.format(
            table_cols = table_cols,
            table_name = self.data_table["name"]
        )
        base_infos = query_info(query_sql)
        infos = []
        keys = list(self.data_table["match"].keys())
        for base_info in base_infos:
            tmp = {}
            for index in range(0,len(keys)):
                tmp[keys[index]] = base_info[index]
            infos.append(tmp)
        return infos
    
    def create_reqparser(self):
        parser = reqparse.RequestParser()
        for key,values in self.data_table["match"].items():
            type_name = values.split("|")[1]
            parser.add_argument("\"{key}\"".format(key=key),type=type_name,required=True,help="{key}输入有误！".format(key=key))
        args = parser.parse_args()        
        return args

    def create_insert_sql(self,args):
        # ### STEP 1: create reqparser ###
        # args = self.create_reqparser()
        ### STEP 2: create insert sql ###
        table_cols = ""
        table_values = ""
        for key,values in self.data_table["match"].items():
                values = values.split('|')[0]
                table_cols = table_cols + values + ","
                if key == "md_time":
                    table_values = table_values + "\"" + get_current_time() + "\"" + ","
                else:
                    if type(args.get(key)) == int or type(args.get(key)) == float:
                        table_values = table_values + str(args.get(key)) + ","
                    elif type(args.get(key)) == str:
                        table_values = table_values + "\"" + args.get(key) + "\"" + ","
                    else:
                        table_values = table_values + "\"" + str(args.get(key)) + "\"" + ","
        table_cols = table_cols.rstrip(",")
        table_values = table_values.rstrip(",")
        ### SQL：add qc infos to database ###
        add_sql = '''
            INSERT INTO {table_name}(
                {table_cols}
            ) VALUES (
                {table_values}
            )
        '''.format(
            table_name = self.data_table["name"],
            table_cols = table_cols,
            table_values = table_values
        )
        return add_sql

    def create_query_sql(self,cols,table_name):
        query_sql = '''
            SELECT 
                {cols}
            FROM {table_name}
        '''.format(
            cols = cols,
            table_name = table_name
        )
        return query_sql
        
    def create_put_sql(self,args):
        ### STEP 2: create insert sql ###
        table_cols = ""
        table_values = ""
        for key,values in self.data_table["match"].items():
            print(key,values)
        ### SQL：add extraction qc infos to database ###
        
sample_db = {
    "name": "sms_sample",
    "match":{
        "proj_id":"project_id|str",
        "sam_id":"sample_id|str",
        "sam_source":"sample_source|str",
        "sam_type":"sample_type|str",
        "sam_name":"sample_name|str",
        # "cre_time":"create_time",
        "op_user":"operate_user|str",
        "ch_user":"check_user|str"
    }
}

sample_log = {
    "name": "sms_sample_log",
    "match":{
        "act_id":"action_id|str",
        "sam_id":"sample_id|str",
        "op_user":"operate_user|str",
        "ch_user":"check_user|str",
        "act_type":"action_type|str",
        "op_time":"operate_time|str",
        "cre_time":"create_time|str",
        "md_time":"modify_time|str"
    }
}

sample_amount = {
    "name": "sms_sample",
    "match":{
        "proj_id":"project_id",
        "sam_id":"sample_id",
        "init_amount":"init_amount",
        "cur_amount":"cur_amount",
        # "cre_time":"create_time",
        # "op_user":"operate_user",
        # "ch_user":"check_user"
    }
}

extraction_db = {
    "name": "ngs_extraction",
    "match":{
        "ext_id":"extraction_id",
        "stc_id":"stock_id",
        "sam_id":"sample_id",
        "op_user":"operate_user",
        "ch_user":"check_user",
        # "op_time":"operate_time",
        # "cre_time":"create_time"
    }
}

extraction_qc_db ={
    "name": "ngs_extraction_qc",
    "match":{
        "proj_id":"project_id|str",
        "ext_id":"extraction_id|str",
        "sam_id":"sample_id|str",
        "stock_amount":"stock_amount|int",
        "nucleic_type":"nucleic_type|str",
        "qc_result":"qc_result|str",
        "DNA_residue":"DNA_residue|str",
        "concen":"concen|float",
        "volume":"volume|float",
        "A260":"A260|int",
        "A280":"A280|int",
        "RIN":"RIN|int",
        "Total_amount":"Total_amount|float",
        "qc_result":"qc_results|str",
        "op_user":"operate_user|str",
        "ch_user":"check_user|str",
        "cre_time":"create_time|str",
        "md_time":"modify_time|str"
    }
}

sequencing_db = {
    "name": "ngs_sequencing",
}

ngsLib_db = {
    "name": "ngs_library",
    "match":{
        "ext_id":"extraction_id|str",
        "stc_id":"stock_id|str",
        "sam_id":"sample_id|str",
        "op_user":"operate_user|str",
        "ch_user":"check_user|str",
        "op_time":"operate_time|str",
        "cre_time":"create_time|str"
    }
}

ngsLib_qc_db = {
    "name": "ngs_library_qc",
    "match":{
        "lib_id":"library_id|str",
        "sam_id":"sample_id|str",
        "ext_id":"extraction_id|str",
        "stock_amount": "stock_amount|int",
        "PCR_circles":"PCR_circles|int",
        "concen":"concen|int",
        "volume":"volume|int",
        "Total_amount":"Total_amount|int",
        "Index_id":"Index_id|str",
        "fragment_size":"fragment_size|int",
        "qc_result":"qc_result|str",
        "op_user":"operate_user|str",
        "ch_user":"check_user|str",
        "cre_time":"create_time",
        "md_time":"modify_time|str"
    }
}

stock_ngs_extraction = {
    "name":"sms_stock_ngs_extraction",
}

sample_stock_db = {
    "name": "sms_stock_raw",
    "match":{
        "sam_id":"sample_id|str",
        "":""
    }
}

project_db = {
    "name": "pms_project",
    "match":{
        # "id":"id|int",
        "proj_id":"project_id|str",
        "cli_id":"client_id|str",
        "prod_id":"product_id|str",
        "prot_id":"protocol_id|str",
        "proj_ty":"project_type|int",
        "proj_le":"project_level|int",
        "status":"status|int",
        "sam_rec_id":"sample_receive_id|str"
    }
}

bioana_db = {
    "name": "ams_ngs_analysis",
    "match":{
        "":"",
        "":""
    }
}

bioana_rel_db = {
    "name": "ams_library_analysis"
}

role_db = {
    "name": "roles",
    "match":{
        "role_id":"name"
    }
}

user_db = {
    "name": "users",
    "match":{
        "role_id":"role_id",
        "username":"username"
    }
}

ngs_op_log = {
    "name": "sys_ngs_log",
    "match":{
        
    }
}